﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Objects.DataClasses;

using AutoMapper;
using Httlgd.DataCenter.Services.Common;
using System.Reflection;

namespace Httlgd.DataCenter.Services.Core
{
    /// <summary>
    /// Base class for Data Access
    /// </summary>
    /// <typeparam name="EntityBase">Entity type</typeparam>    
    public abstract class DataAccessorBase<BusinessEntityType, EntityType, EFContextType> 
        : IDataAccessor<BusinessEntityType>
        where BusinessEntityType : BusinessEntityBase, new()
        where EntityType : EntityBase, new()
        where EFContextType : ObjectContextBase, new()
    {
        protected EFContextType objectContext;

        #region Properties
        /// <summary>
        /// Set name of the entity (table name in DB)
        /// </summary>
        public string EntitySetName
        {
            get;
            private set;
        }

        /// <summary>
        /// Name of key column
        /// </summary>
        public string KeyColumnName
        {
            get;
            private set;
        }
        #endregion

        #region Constructors
        public DataAccessorBase()
            : this(new EFContextType())
        {
        }

        public DataAccessorBase(EFContextType EFContext)
        {
            objectContext = EFContext;

            EntityType entity = new EntityType();
            EntityKey newKey = this.objectContext.CreateEntityKey(this.objectContext.GetType().Name + "." + typeof(EntityType).Name, entity);

            this.EntitySetName = this.objectContext.GetType().Name + "." + typeof(EntityType).Name;

            if (newKey.EntityKeyValues != null)
            {
                this.KeyColumnName = newKey.EntityKeyValues.First().Key;
            }

        }
        #endregion

        #region IDataAccessor<EntityBase> Members

        public virtual void Delete(object key)
        {
            EntityType entity = this.RetrieveEntity(key);
            this.objectContext.DeleteObject(entity);
            this.objectContext.SaveChanges();
        }

        /// <summary>
        /// use Reflection?
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual BusinessEntityType Insert(BusinessEntityType businessEntity)
        {
            EntityType entity = this.MapBusinessEntityToEntity(businessEntity, null);
            this.objectContext.AddObject(EntitySetName, entity);
            this.objectContext.SaveChanges();

            this.UpdateAssociatedData(businessEntity, entity);

            return this.MapEntityToBusinessEntity(entity, null);
        }

        public virtual BusinessEntityType Update(BusinessEntityType businessEntity)
        {
            EntityType entity = null;
            entity = this.MapBusinessEntityToEntity(businessEntity, entity);

            if (entity == null)
            {
                throw new Exception();
            }

            EntityKey key = this.objectContext.CreateEntityKey(this.EntitySetName, entity);
            object originalItem;
            if (this.objectContext.TryGetObjectByKey(key, out originalItem))
            {
                this.objectContext.ApplyCurrentValues(key.EntitySetName, entity);
            }

            try
            {
                this.objectContext.SaveChanges();
            }
            catch (OptimisticConcurrencyException ex)
            {
                throw ex;
            }

            // Insert in case it NOT insert before
            this.UpdateAssociatedData(businessEntity, entity);
            this.objectContext.SaveChanges();

            //Update the business entity with updated value of entity after saving.
            //Returning with a new object
            return this.MapEntityToBusinessEntity(entity, null);
        }

        public virtual BusinessEntityType Retrieve(object key)
        {
            EntityType entity = this.RetrieveEntity(key);
            return this.MapEntityToBusinessEntity(entity, null);
        }

        public virtual List<BusinessEntityType> RetrieveAll()
        {
            List<BusinessEntityType> list = new List<BusinessEntityType>();

            string queryString = @"SELECT VALUE obj FROM " + this.EntitySetName + " AS obj";

            var query = this.objectContext.CreateQuery<EntityType>(queryString);

            foreach (EntityType entity in query)
            {
                list.Add(this.MapEntityToBusinessEntity(entity, null));
            }

            return list;
        }
        #endregion

        #region Mapping
        #region Mapping Entity To Business Entity
        /// <summary>
        /// Map an entity to a business entity object
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="businessEntity"></param>
        /// <returns></returns>
        public BusinessEntityType MapEntityToBusinessEntity(EntityType entity, BusinessEntityType businessEntity)
        {
            businessEntity = this.MapEntityToBusinessEntity_Scalar(entity, businessEntity);

            if (businessEntity == null)
            {
                return null;
            }

            return MapEntityToBusinessEntity_ForChildren(entity, businessEntity);
        }

        protected BusinessEntityType MapEntityToBusinessEntity_Scalar(EntityType entity, BusinessEntityType businessEntity)
        {
            businessEntity = ValidateBeforeMapEntityToBusinessEntity(entity, businessEntity);
            
            if (businessEntity == null)
            {
                return null;
            }

            this.MapEntityToBusinessEntity_ScalarInner(entity, businessEntity);

            if (businessEntity == null)
            {
                return null;
            }

            businessEntity.Key = entity.Key;
            //businessEntity.RecordTimestamp = entity.GetRecordTimestamp;
            businessEntity.LastUpdateDate = entity.GetLastUpdateDate;
            businessEntity.CreateDate = entity.GetCreateDate;

            return businessEntity;
        }

        protected virtual BusinessEntityType MapEntityToBusinessEntity_ScalarInner(EntityType entity, BusinessEntityType businessEntity)
        {
            List<string> allowSrcL = typeof(EntityType)
                .GetProperties()
                .Where(r =>
                            (Attribute.GetCustomAttribute(r, typeof(EdmScalarPropertyAttribute), false) != null
                            && r.Name != EntityBase.CreateDatePropertyName
                            && r.Name != EntityBase.LastUpdateDatePropertyName
                                /*&& r.Name != EntityBase.RecordTimestampPropertyName*/))
                .Select(r => r.Name)
                .ToList();

            IMappingExpression<EntityType, BusinessEntityType> mapperExt = Mapper.CreateMap<EntityType, BusinessEntityType>();

            List<PropertyInfo> ignoreDestList = typeof(BusinessEntityType).GetProperties().Where(r => !allowSrcL.Contains(r.Name)).ToList();
            foreach (PropertyInfo item in ignoreDestList)
            {
                mapperExt = mapperExt.ForMember(item.Name, opt => opt.Ignore());
            }

            // Perform mapping for patient
            businessEntity = Mapper.Map<EntityType, BusinessEntityType>(entity, businessEntity);

            return businessEntity;
        }

        protected virtual BusinessEntityType MapEntityToBusinessEntity_ForChildren(EntityType entity, BusinessEntityType businessEntity)
        {
            return businessEntity;
        }
        #endregion

        #region Mapping Business Entity to Entity

        /// <summary>
        /// Map a Business Entity to Entity
        /// </summary>
        /// <param name="businessEntity"></param>
        /// <param name="entity"></param>
        /// <returns></returns>
        public EntityType MapBusinessEntityToEntity(BusinessEntityType businessEntity, EntityType entity)
        {
            entity = this.MapBusinessEntityToEntity_Scalar(businessEntity, entity);

            if(entity == null)
                return null;

            return MapBusinessEntityToEntity_ForChildren(businessEntity, entity);
        }

        protected EntityType MapBusinessEntityToEntity_Scalar(BusinessEntityType businessEntity, EntityType entity)
        {
            entity = ValidateBeforeMapBusinessEntityToEntity(businessEntity, entity);

            if(entity == null)
                return null;

            return MapBusinessEntityToEntity_ScalarInner(businessEntity, entity);
        }

        protected virtual EntityType MapBusinessEntityToEntity_ScalarInner(BusinessEntityType businessEntity, EntityType entity)
        {
            IMappingExpression<BusinessEntityType, EntityType> mapperExt = Mapper.CreateMap<BusinessEntityType, EntityType>();

            List<PropertyInfo> ignoreDestL = typeof(EntityType)
                .GetProperties()
                .Where(r => !
                            (Attribute.GetCustomAttribute(r, typeof(EdmScalarPropertyAttribute), false) != null
                            && ((EdmScalarPropertyAttribute)Attribute.GetCustomAttribute(r, typeof(EdmScalarPropertyAttribute), false)).EntityKeyProperty == false
                            && r.Name != EntityBase.CreateDatePropertyName
                            && r.Name != EntityBase.LastUpdateDatePropertyName
                            /*&& r.Name != EntityBase.RecordTimestampPropertyName*/))
                .ToList();

            foreach (PropertyInfo item in ignoreDestL)
            {
                mapperExt = mapperExt.ForMember(item.Name, opt => opt.Ignore());
            }

            // Perform mapping for patient
            Mapper.Map<BusinessEntityType, EntityType>(businessEntity, entity);

            return entity;
        }

        protected virtual EntityType MapBusinessEntityToEntity_ForChildren(BusinessEntityType businessEntity, EntityType entity)
        {
            return entity;
        }

        #endregion

        #region Mapping validation
        /// <summary>
        /// Validates Before Maping Business Entity To Entity.
        /// </summary>
        /// <param name="businessEntity">input business entity</param>
        /// <param name="entity">input data entity</param>
        /// <returns>output data entity</returns>
        public virtual EntityType ValidateBeforeMapBusinessEntityToEntity(BusinessEntityType businessEntity, EntityType entity)
        {
            // No BE, no mapping
            if (businessEntity == null)
            {
                return null;
            }

            if (entity == null)
            {
                if (businessEntity.HasKey)
                {
                    entity = this.RetrieveEntity(businessEntity.Key);
                    if (entity == null)
                    {
                        return null;
                    }
                }
                else
                {
                    entity = new EntityType();
                }
            }

            // Can NOT map two entity with different Key.
            /*if (entity.Key != null)
            {
                if (businessEntity.Key.ToString() != entity.Key.ToString())
                {
                    return null;
                }

                if (businessEntity.RecordTimestamp != null &&
                    !businessEntity.RecordTimestamp.SequenceEqual(entity.GetRecordTimestamp))
                {
                    throw new SMARTApplicationException(ValidationMessages.SystemUpdateFailed);
                }
            }*/

            return entity;
        }

        public virtual BusinessEntityType ValidateBeforeMapEntityToBusinessEntity(EntityType entity, BusinessEntityType businessEntity)
        {
            // Entity is null. No Map
            if (entity == null || entity.Key == null)
            {
                return null;
            }

            // Enity is New. No Map
            if (entity.EntityState == EntityState.Added)
            {
                return null;
            }

            if (businessEntity == null)
            {
                businessEntity = new BusinessEntityType();
            }

            // Different Key. No Map
            if (businessEntity.HasKey &&
                (businessEntity.Key.ToString() != entity.Key.ToString()))
            {
                return null;
            }

            return businessEntity;
        }
        #endregion
        #endregion

        #region transaction
        //public virtual EntityType Retrieve(SqlConnection connection, SqlTransaction transaction, int ID)
        //{
        //    string query = string.Format("SELETE * FROM {0} WHERE ID = {1}", TableName, ID);
        //    EntityType entity = null;

        //    using (SqlDataReader reader = this.ExecuteReader(connection, transaction, query, System.Data.CommandType.Text, null))
        //    {
        //        while (reader.Read())
        //        {
        //            entity = new EntityType();
        //            entity.PopulateData(reader);
        //            break;
        //        }
        //    }

        //    return entity;
        //}

        //public virtual EntityType Insert(SqlConnection connection, SqlTransaction transaction, EntityType entity)
        //{
        //    throw new Exception("Method not implemented.");
        //}
        //EntityType Update(SqlConnection connection, SqlTransaction transaction, EntityType entity);
        #endregion

        #region Query/Store procedure execution helpers
        protected int ExecuteNonQuery(string query, System.Data.CommandType commandType, SqlParameter[] parameters)
        {
            SqlConnection conn = SQLHelper.GetConnection();
            SqlCommand cmd = null;
            int affectedCount = 0;

            try
            {
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.CommandType = commandType;

                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);

                affectedCount = cmd.ExecuteNonQuery();
            }
            catch
            {
                //logging here
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                conn.Close();
                conn.Dispose();
            }

            return affectedCount;
        }

        protected SqlDataReader ExecuteReader(string query, System.Data.CommandType commandType, SqlParameter[] parameters)
        {
            SqlConnection conn = SQLHelper.GetConnection();
            SqlCommand cmd = null;
            SqlDataReader reader = null;

            try
            {
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.CommandType = commandType;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);

                reader = cmd.ExecuteReader();
            }
            catch
            {
                //logging here
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                conn.Close();
                conn.Dispose();
            }

            return reader;
        }

        protected object ExecuteScalar(string query, System.Data.CommandType commandType, SqlParameter[] parameters)
        {
            SqlConnection conn = SQLHelper.GetConnection();
            SqlCommand cmd = null;
            object result = null;

            try
            {
                conn.Open();
                cmd = new SqlCommand(query, conn);
                cmd.CommandType = commandType;
                if (parameters != null)
                    cmd.Parameters.AddRange(parameters);
                result = cmd.ExecuteScalar();
            }
            catch
            {
                //logging here
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();

                conn.Close();
                conn.Dispose();
            }

            return result;
        }
        #endregion

        #region execution with transaction helpers
        protected int ExecuteNonQuery(SqlConnection connection, SqlTransaction transaction, string query, System.Data.CommandType commandType, SqlParameter[] parameters)
        {
            if (connection == null) return -1;

            SqlCommand cmd = null;
            int affectedCount = 0;

            if (connection.State == System.Data.ConnectionState.Closed)
                connection.Open();

            if (transaction == null)
                transaction = connection.BeginTransaction();

            cmd = new SqlCommand(query, connection);
            cmd.Transaction = transaction;
            cmd.CommandType = commandType;

            if (parameters != null)
                cmd.Parameters.AddRange(parameters);

            affectedCount = cmd.ExecuteNonQuery();


            return affectedCount;
        }

        protected SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, string query, System.Data.CommandType commandType, SqlParameter[] parameters)
        {

            SqlCommand cmd = null;
            SqlDataReader reader = null;
            if (connection.State == System.Data.ConnectionState.Closed)
                connection.Open();

            if (transaction == null)
                transaction = connection.BeginTransaction();

            cmd = new SqlCommand(query, connection);
            cmd.CommandType = commandType;
            cmd.Transaction = transaction;

            if (parameters != null)
                cmd.Parameters.AddRange(parameters);

            reader = cmd.ExecuteReader();


            return reader;
        }

        protected object ExecuteScalar(SqlConnection connection, SqlTransaction transaction, string query, System.Data.CommandType commandType, SqlParameter[] parameters)
        {
            if (connection == null) return -1;
            SqlCommand cmd = null;
            object result = null;


            if (connection.State == System.Data.ConnectionState.Closed)
                connection.Open();

            if (transaction == null)
                transaction = connection.BeginTransaction();

            cmd = new SqlCommand(query, connection);
            cmd.CommandType = commandType;
            cmd.Transaction = transaction;

            if (parameters != null)
                cmd.Parameters.AddRange(parameters);
            result = cmd.ExecuteScalar();

            return result;
        }
        #endregion

        public virtual EntityType RetrieveEntity(object key)
        {
            try
            {
                return this.objectContext.GetObjectByKey(this.GetEntityKey(key)) as EntityType;
            }
            catch (ObjectNotFoundException)
            {
                return null;
            }
        }

        public virtual void UpdateAssociatedData(BusinessEntityType businessEntity, EntityType entity)
        {
        }

        private EntityKey GetEntityKey(object key)
        {
            IEnumerable<KeyValuePair<string, object>> entityKeyValues
                = new KeyValuePair<string, object>[] { new KeyValuePair<string, object>(this.KeyColumnName, key) };

            return new EntityKey(this.EntitySetName, entityKeyValues);
        }


        public virtual bool IsExisted(object key)
        {
            return (this.RetrieveEntity(key) != null);
        }
    }
}
